# -*- coding: utf-8 -*-
# @Time    : 2019/2/25 14:04
# @Author  : Macher
# @File    : shorturl.py
# @Software: PyCharm Community Edition
# 处理钓鱼网站的URL变成IP
import pymysql
import re
import socket

sqlshorturl = 'update phish set shorturl = %s where pid = %s'
sql_insert = "insert into phish_ip(ip,shorturl)values( %(ip)s,%(shorturl)s)"

config = {'host': '219.216.65.41',
          'user': 'root',
          'password': '123456',
          'port': 3306,
          'database': 'mydata',
          'charset': 'utf8'}
# db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='mydata')
def dealUrl(s1):
    try:
        pattern = re.compile('//(.*?)/')
        url = "".join(pattern.findall(s1))
        if url == '':
            url = s1.split('//')[1]
    except:
        pattern = re.compile('//(.*?)')
        url = "".join(pattern.findall(s1))
    print(url)
    return url

def geturl():          #插入数据库的函数
    db = pymysql.connect(host='219.216.65.41', user='root', password='123456', port=3306, db='mydata')
    cursor = db.cursor()
    # sql = 'SELECT pid,url FROM phish WHERE ip = "" limit 10000'
    sql = 'SELECT shorturl FROM phish WHERE shorturl is not null '
    cursor.execute(sql)
    results = cursor.fetchall()
    new = []
    for row in results:
        new.append(row[0])
    new = list(set(new))
    cursor.close()
    # db.commit()
    # db.close()
    # print(new)
    # print(len(new))
    return new


def getshorturl():          #插入数据库的函数
    db = pymysql.connect(host='219.216.65.41', user='root', password='123456', port=3306, db='mydata')
    cursor = db.cursor()
    sql = 'SELECT pid,shorturl FROM phish where ip is null group  by  shorturl'
    cursor.execute(sql)
    results = cursor.fetchall()
    new = []
    for row in results:
        temp = []
        # url = "".join(dealUrl(row[1]))
        temp.append(row[1])
        temp.append(row[0])

        new.append(temp)
    # new = list(set(new))
    cursor.close()
    # print(new)
    return new

def insertMysql(list):
    db = pymysql.connect(host='219.216.65.41', user='root', password='123456', port=3306, db='mydata')
    cursor = db.cursor()
    cursor.execute(sql_insert, list)
    cursor.close()
    db.commit()
    db.close()

def updataSql(sql,s):          #插入数据库的函数
    db = pymysql.connect(host='219.216.65.41', user='root', password='123456', port=3306, db='mydata')
    cursor = db.cursor()
    cursor.execute(sql,s)
    cursor.close()
    # db.commit()
    # db.close()

def shirtUrl(results):
    new = []
    while results:
    # for row in results:
    #     temp = []
        print('last:',len(results))
        row = results.pop()
        shorturl  = tuple({str(dealUrl(row[1]))})
        pid = tuple({str(row[0])})
        print((shorturl + pid))
        updataSql(sqlshorturl, (shorturl + pid))



def get_ip_list(domain_lsit):  # 获取域名解析出的IP列表
        ip_list = []
        while domain_lsit:
            domain = domain_lsit.pop()
            try:
                addrs = socket.getaddrinfo(domain, None)
                for item in addrs:
                    if item[4][0] not in ip_list:
                        ip_list.append(item[4][0])
                        if recompare(item[4][0]):
                            dict1 = {}
                            dict1['ip'] = str(item[4][0])
                            dict1['shorturl'] = str(domain)
                            insertMysql(dict1)
                            print(item[4][0],domain)
            except Exception as e:
                # print(str(e))
                pass
        ip_list = list(set(ip_list))
        print(ip_list)
        print(len(ip_list))
            # return ip_list
# shirtUrl(geturl())
def recompare(ip):
    if re.match(r"^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$",ip):
        return True

get_ip_list(geturl())